﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Forms;

namespace SharePointDatabaseExplorer
{
    class SharePoint
    {
        private int _ID;
        private string _DocID;
        private string _ListID;
        private string _SiteID;
        private int _VersionID;
        public static string FilePath;

        public int ID
        {
            get
            {
                return _ID;
            }
            set
            {
                _ID = value;
            }
        }

        public string DocID
        {
            get
            {
                return _DocID;
            }
            set
            {
                _DocID = value;
            }
        }

        public string ListID
        {
            get
            {
                return _ListID;
            }
            set
            {
                _ListID = value;
            }
        }
        public string SiteID
        {
            get
            {
                return _SiteID;
            }
            set
            {
                _SiteID = value;
            }
        }

        public int VersionID
        {
            get
            {
                return _VersionID;
            }
            set
            {
                _VersionID = value;
            }
        }

        public Boolean ExportFile(int Version)
        {
            SetDocumentID();
            string sSQL = "SELECT DISTINCT ad.tp_Version AS InternalVersion, alldoc.DirName, alldoc.LeafName, ads.Content " +
                            "FROM AllUserData ad, AllDocVersions adv, AllDocStreams ads, AllDocs alldoc " +
                            "WHERE ad.tp_ListId = '" + this.ListID + "' and ad.tp_Id = " + this.ID + " AND ad.tp_Version = " + Version + " " +
                            "AND ad.tp_DocID = adv.Id " +
                            "AND ads.Id = ad.tp_DocId " +
                            "AND ads.InternalVersion = adv.InternalVersion " +
                            "AND ad.tp_UIVersion = adv.UIVersion " +
                            "AND alldoc.Id = ad.tp_DocId ";                            

            SqlDataReader reader;
            reader = Database.GetReader(sSQL);
            Boolean bHasRows = reader.HasRows;

            if (!bHasRows)
            {
                sSQL = "SELECT TOP 1 max(ads.InternalVersion) as InternalVersion, ad.DirName, ad.LeafName, ads.Content " +
                            "FROM AllDocs ad, AllDocStreams ads " +
                            "WHERE ad.SiteId = ads.SiteId " +
                            "AND ad.Id = ads.Id " +
                            "AND ads.Content IS NOT NULL " +
                            "AND ad.Id = '" + this.DocID + "'" +
                            "GROUP BY ad.DirName, ad.LeafName, ads.Content " +
                            "ORDER BY InternalVersion DESC";
                reader = Database.GetReader(sSQL);
                bHasRows = reader.HasRows;
            }

            if (bHasRows)
            {
                return CreateFile(reader);
            }
            else
            {
                return false;
            }
        }

        public Boolean ExportFile()
        {
            SetDocumentID();
            
            string sSQL = "SELECT TOP 1 max(ads.InternalVersion) as InternalVersion, ad.DirName, ad.LeafName, ads.Content " +
                            "FROM AllDocs ad, AllDocStreams ads " +
                            "WHERE ad.SiteId = ads.SiteId " +
                            "AND ad.Id = ads.Id " +
                            "AND ads.Content IS NOT NULL " +
                            "AND ad.Id = '" + this.DocID + "'" +
                            "GROUP BY ad.DirName, ad.LeafName, ads.Content " +
                            "ORDER BY InternalVersion DESC";

            SqlDataReader reader = Database.GetReader(sSQL);
            return CreateFile(reader);

        }

        private bool CreateFile(SqlDataReader reader)
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    // grab the file’s directory and name
                    string FileDirName = (string)reader["DirName"];
                    string DirName = Database._FileOutputPath + "/" + FileDirName;
                    string LeafName = (string)reader["LeafName"];

                    if (this._VersionID == 0)
                    {
                        FilePath = DirName + @"\" + LeafName;
                    }
                    else
                    {
                        string[] sFile = LeafName.Split('.');

                        FilePath = DirName + @"\" + sFile[0].ToString() + @"_Version" + this.VersionID + "." + sFile[1];
                    }
                    // create directory for the file if it doesn’t yet exist
                    if (!Directory.Exists(DirName))
                    {
                        Directory.CreateDirectory(DirName);
                        Console.WriteLine("Creating directory: " + DirName);
                    }

                    FileStream fs = new FileStream(FilePath, FileMode.Create, FileAccess.Write);
                    BinaryWriter writer = new BinaryWriter(fs);

                    // depending on the speed of your network,
                    // you may want to change the buffer size (it’s in bytes)
                    int bufferSize = 1000000;
                    long startIndex = 0;
                    long retval = 0;
                    byte[] outByte = new byte[bufferSize];

                    // grab the file out of the db one chunk
                    // (of size bufferSize) at a time
                    do
                    {
                        retval = reader.GetBytes(3, startIndex, outByte, 0,
                            bufferSize);
                        startIndex += bufferSize;

                        writer.Write(outByte, 0, (int)retval);
                        writer.Flush();
                    } while (retval == bufferSize);

                    // finish writing the file
                    writer.Close();
                    fs.Close();
                }

                // close the DB connection and whatnots
                reader.Close();

                return true;
            }
            else
            {
                return false;
            }
        }

        private void SetDocumentID()
        {
            string sQuery = "select DISTINCT tp_DocId from AllUserData where tp_Id = " + this.ID + " and tp_listId = '" + this.ListID + "'";
            DataSet dsDocumentID = Database.RunQuery(sQuery);
            this.DocID = dsDocumentID.Tables[0].Rows[0]["tp_DocId"].ToString();
        }

        public static void ExportMetadataToCSV(DataGridView gridIn, string outputFile)
        {
            if (gridIn.RowCount > 0)
            {
                string value = "";
                DataGridViewRow dr = new DataGridViewRow();
                StreamWriter swOut = new StreamWriter(outputFile);

                //write header rows to csv
                for (int i = 0; i <= gridIn.Columns.Count - 1; i++)
                {
                    if (i > 0)
                    {
                        swOut.Write(",");
                    }
                    swOut.Write(gridIn.Columns[i].HeaderText);
                }

                swOut.WriteLine();

                //write DataGridView rows to csv
                for (int j = 0; j <= gridIn.Rows.Count - 1; j++)
                {
                    if (j > 0)
                    {
                        swOut.WriteLine();
                    }

                    dr = gridIn.Rows[j];
                    for (int i = 0; i <= gridIn.Columns.Count - 1; i++)
                    {
                        if (i > 0)
                        {
                            swOut.Write(",");
                        }

                        value = dr.Cells[i].Value.ToString();
                        //replace comma's with spaces 
                        value = value.Replace(',', ' ');
                        //replace embedded newlines with spaces
                        value = value.Replace(Environment.NewLine, " ");

                        swOut.Write(value);
                    }
                }
                swOut.Close();
            }
        }        
    }
}
